package com.itestor.iptv.modules.common.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;

import com.itestor.iptv.global.dao.GenericHibernateDao;
import com.itestor.iptv.modules.common.domain.RegionManage;
import com.itestor.iptv.modules.common.vo.RegionManageBean;
import com.itestor.iptv.utils.JdbcUtil;

/**
 * @author Mr.li 
 */
public class TbRegionManageDao extends GenericHibernateDao<RegionManage, Integer> {

    /**
     * 按区局名称查找.
     * @param regionName 
     * @return RegionManage 用户
     */
    public RegionManage find(String regionName) {
        StringBuilder hql = new StringBuilder();
        hql.append("from RegionManage t where t.regionName=:regionName");
        
        Query query = getHibernateSession().createQuery(hql.toString());
        query.setParameter("regionName", regionName);
        
        return getEntity(query);
    }
    
    /**
     * 
     * @param parentRegionId
     * @return List
     */
    public List<RegionManageBean> getChildRegionList(Integer parentRegionId) {
        StringBuilder sql = new StringBuilder();
        sql.append("{call pr_regionmanage_gettree(?)}");        
        Session session = getHibernateSession();
        Connection conn =session.connection();
        CallableStatement  callstmt=null;
        ResultSet rs =null;
        List<RegionManageBean>  regionList=new ArrayList<RegionManageBean>();
        
        try {
            callstmt =conn.prepareCall(sql.toString());
            callstmt.setInt(1,parentRegionId);
            rs =callstmt.executeQuery();
            while(rs.next()){
                RegionManageBean  region = new RegionManageBean();
                region.setIdRegion(rs.getInt("idRegion"));
                region.setParentRegionId(rs.getInt("ParentRegionID"));
                region.setRegionName(rs.getString("RegionName"));
                region.setRegionLevel(rs.getInt("RegionLevel"));
                
                regionList.add(region);
            }            
        } catch (SQLException e) {           
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs,callstmt, conn);
        }
        
        return regionList;
    }
    
    
    /**
     * 获取顶级区域
     * @return List.
     */
    @SuppressWarnings("unchecked")
    public List<RegionManage> getRootRegionList(){
        StringBuilder sql = new StringBuilder();
        sql.append(" from RegionManage t where t.prentRegionId = -1");
        Query query = getHibernateSession().createQuery(sql.toString());
        
        return query.list();           
    }
    /**
     * 获取区局列表.
     * @return Map
     */
    public Map<Integer, RegionManage> getBelongToRegionMap() {
        StringBuilder sql = new StringBuilder();
        sql.append(" from RegionManage t order by t.prentRegionId ");        
        Query query = getHibernateSession().createQuery(sql.toString());        
        Map<Integer,RegionManage> regionMap=new HashMap<Integer, RegionManage>();
        
        List<RegionManage> list=(List<RegionManage>)query.list();
        for(RegionManage region : list){
            regionMap.put(region.getIdRegion(), region);
        }
        
        return regionMap;
    }
    
    @SuppressWarnings("unchecked")
	public List<RegionManage> getRegionListByParentId(Integer prentRegionId){
    	StringBuilder sql = new StringBuilder();
        sql.append(" from RegionManage t where t.prentRegionId = :prentRegionId");        
        Query query = getHibernateSession().createQuery(sql.toString()).setParameter("prentRegionId", prentRegionId);
        return query.list();      
    }
    
}